package com.city.repository.report;

import com.city.domain.city.CityEventType;
import com.city.domain.report.CtReport;
import com.city.format.CtReportFormat;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.List;

@Repository
public interface CtReportRepository extends JpaRepository<CtReport,Integer> {

    @Query(value =  "SELECT * FROM ct_report " +
                    "WHERE report_id = ?1 " +
                    "ORDER BY create_time DESC",nativeQuery = true)
    List<CtReport> findALlReportByReportId(Integer reportId);


    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET branch_id = ?2 " +
                    ",schedule = ?3 " +
                    "WHERE id = ?1",nativeQuery = true)
    void dispatchBranch(Integer reportId, Integer branchId, Integer schedule);

    /**
     * @author  王宇
     * @date 2018/9/9
     * @description  返工
     */
    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET isrework = ?2 " +
            ",schedule = ?3 " +
            "WHERE id = ?1",nativeQuery = true)
    void reWork(Integer reportId, Integer isrework, Integer schedule);


    /**
     * @author  王宇
     * @date 2018/9/18
     * @description  案件回撤
     */
    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET iscaseback = ?2 " +
            ",schedule = ?3 " +
            "WHERE id = ?1",nativeQuery = true)
    void caseBack(Integer reportId, Integer isrework, Integer schedule);


    /**
     * @author  王宇
     * @date 2018/9/9
     * @description  设置 处理人
     */
    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET check_man_id = ?2 " +
            ",schedule = ?3 " +
            "WHERE id = ?1",nativeQuery = true)
    void dispatchPeople(Integer reportId, Integer peopleId, Integer schedule);

    /**
     * @author  王宇
     * @date 2018/9/9
     * @description  设置审核通过
     */
    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET hecha_id = ?2 " +
            ",schedule = ?3 " +
            "WHERE id = ?1",nativeQuery = true)
    void checkPass(Integer reportId, Integer hechaId, Integer schedule);


    /**
     * @author  王宇
     * @date 2018/9/9
     * @description  设置结案人
     */
    @Transactional
    @Modifying
    @Query(value =  "UPDATE ct_report SET finish_man_id = ?2 " +
            ",schedule = ?3 " +
            "WHERE id = ?1",nativeQuery = true)
    void checkAccepted(Integer reportId, Integer finishId, Integer schedule);


    List<CtReport> findAll();


    @Query(value =  "SELECT * FROM ct_report " +
                    "WHERE schedule > ?1 " +
                    "AND schedule < ?2",nativeQuery = true)
    List<CtReport> findAllByScheduleLimit(Integer left, Integer right);


    @Query(value =  "SELECT * FROM ct_report " +
            "WHERE schedule > ?1 " +
            "AND schedule < ?2 and branch_id=?3",nativeQuery = true)
    List<CtReport> findAllReportByScheduleByUserId(Integer left, Integer right,Integer branchId);


    @Query(value = "SELECT count(*) FROM ct_report WHERE branch_id = ? AND schedule = ?",nativeQuery = true)
    Integer findAllByBranchIdAndSchedule(Integer branchId,Integer schedule);

    @Query(value = "SELECT count(*) FROM ct_report WHERE branch_id = ?",nativeQuery = true)
    Integer countAllByBranchId(Integer branchId);

    @Query(value =  "SELECT * FROM ct_report " +
                    "WHERE branch_id = ?1",nativeQuery = true)
    List<CtReport> findAllByBranchId(Integer branchId);

    @Query(value = "SELECT count(*) FROM ct_report WHERE town_id = ?",nativeQuery = true)
    Integer findAllByTownId(Integer town);

    @Query(value = "SELECT count(*) FROM ct_report WHERE schedule = ? AND town_id = ?",nativeQuery = true)
    Integer findAllByScheduleAndTownId(Integer status,Integer town);

    @Query(value = "SELECT * FROM  ct_report WHERE  id  >= ? AND id <= ?",nativeQuery = true)
    List<CtReport> findAllByIdLimit(Integer left,Integer right);


    @Query(value = "SELECT YEAR(update_time) as YEAR from ct_report group by YEAR",nativeQuery = true)
    List<Integer> findAllYear();

    @Query(value = "select MONTH(update_time),count(MONTH(update_time)) from ct_report where YEAR(update_time) = ? AND schedule = ? group by MONTH(update_time)",nativeQuery = true)
    List<String> findAllByYearAndSchedule(Integer year,Integer schedule);

    @Query(value = "select count(MONTH(update_time)) from ct_report where YEAR(update_time) = ? AND schedule = ? AND town_id = ? group by MONTH(update_time)",nativeQuery = true)
    Integer findAllByYearAndScheduleAndTownId(Integer year,Integer schedule,Integer townId);

    @Query(value = " select MONTH(update_time),count(MONTH(update_time)) from ct_report where YEAR(update_time) = ? group by MONTH(update_time)",nativeQuery = true)
    List<String> findAllByYear(Integer year);

    @Query(value = "select MONTH(update_time),count(MONTH(update_time)) from ct_report where YEAR(update_time) = ? AND branch_id = ? AND schedule = ? group by MONTH(update_time)",nativeQuery = true)
    List<String> findAllByYearAndBranchIdAndSchedule(Integer year,Integer branch_id,Integer schedule);

    @Query(value = " select count(*) from ct_report where YEAR(update_time)= ? AND town_id = ?",nativeQuery = true)
    Integer findAllByYearAndTownId(Integer year,Integer townId);

    @Query(value = "select type_big,count(type_big) from ct_report  group by type_big limit 10",nativeQuery = true)
    String[] findAllByTypeBig();

    @Query(value = "select count(*) from ct_report where schedule = ? AND YEAR(update_time) = ?",nativeQuery = true)
    Integer findAllByScheduleAndYear(Integer schedule,Integer year);

    @Query(value = "select count(*) from ct_report where schedule = ? AND YEAR(update_time) = ? AND MONTH(update_time) = ?",nativeQuery = true)
    Integer findAllByScheduleAndMonth(Integer schedule,Integer year,Integer month);

    @Query(value = "select count(*) from ct_report where date(update_time) = curdate() And schedule = ?",nativeQuery = true)
    Integer findAllByScheduleAndDay(Integer schedule);

    //format查询
    @Query(value = "SELECT new com.city.domain.report.CtReport(c,p) form ct_report c,pub_user p where c.check_man_id =p.id",nativeQuery = true)
    List<CtReportFormat> findByFromat();

    //将督办状态切换
    @Transactional
    @Modifying
    @Query(value = "UPDATE ct_report SET is_do_ban = ?1 where id = ?2",nativeQuery = true)
    void updateDoBan(Integer doban ,Integer id);

    //设置截止时间
    @Transactional
    @Modifying
    @Query(value = "UPDATE ct_report SET deadline = ?1 where id = ?2",nativeQuery = true)
    void updateEndTime(String endTime ,Integer id);

    //设置准时完成
    @Transactional
    @Modifying
    @Query(value = "UPDATE ct_report SET isontime=1 where id=?1",nativeQuery = true)
    void updateIsOntime(Integer id);

    @Query(value = "select * from ct_report where task_id= ?1;",nativeQuery = true)
    CtReport findByTKI(String tkid);

    CtReport findByTaskId(String taskId);
}
